﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Osiptel.HECO.BE;
using System.Data.OracleClient;
using System.Data;

namespace Osiptel.HECO.DL
{
    public class ParametroDetalleDL
    {
        public List<ParametroDetalleBE> ObtenerParamsById(int idTabla)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_LISTAR_PARAMS_TABLA";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("pIdTabla", OracleType.Number).Value = idTabla;
                    cmd.Parameters.Add("oListado", OracleType.Cursor).Direction = ParameterDirection.Output;
                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        try
                        {
                            NewConexion.retOpen();
                            cmd.Connection = NewConexion.retConexion();
                            ParametroDetalleBE objParametroDetBE;
                            List<ParametroDetalleBE> listaParametroDet = new List<ParametroDetalleBE>();
                            using (OracleDataReader objDRParametroDet = cmd.ExecuteReader())
                            {
                                while (objDRParametroDet.Read())
                                {
                                    objParametroDetBE = new ParametroDetalleBE();
                                    objParametroDetBE.IdParametro = Convert.ToInt32(objDRParametroDet["IDPARAMETRO"]);
                                    objParametroDetBE.Nombre = Convert.ToString(objDRParametroDet["NOMBRE"]);
                                    objParametroDetBE.FecCre = Convert.ToDateTime(objDRParametroDet["FECCRE"]);
                                    objParametroDetBE.UsuCre = Convert.ToString(objDRParametroDet["USUCRE"]);
                                    listaParametroDet.Add(objParametroDetBE);
                                }
                                objDRParametroDet.Close();
                            }
                            NewConexion.retClose();
                            return listaParametroDet;
                        }
                        catch (Exception ex)
                        {
                            string error = ex.ToString();
                            List<ParametroDetalleBE> listaParametroDet = new List<ParametroDetalleBE>();
                            return listaParametroDet;
                        }
                        finally
                        {
                            if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<ParametroDetalleBE> ObtenerClaseByTipoSubTipo(int tipo, int subTipo)
        {
            try
            {
                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_LISTAR_CLASE_TIPOSUBTIPO";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("pIdTipo", OracleType.Number).Value = tipo;
                    cmd.Parameters.Add("pIdSubTipo", OracleType.Number).Value = subTipo;
                    cmd.Parameters.Add("oListado", OracleType.Cursor).Direction = ParameterDirection.Output;
                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        try
                        {
                            NewConexion.retOpen();
                            cmd.Connection = NewConexion.retConexion();
                            ParametroDetalleBE objParametroDetBE;
                            List<ParametroDetalleBE> listaParametroDet = new List<ParametroDetalleBE>();
                            using (OracleDataReader objDRParametroDet = cmd.ExecuteReader())
                            {
                                while (objDRParametroDet.Read())
                                {
                                    objParametroDetBE = new ParametroDetalleBE();
                                    objParametroDetBE.IdParametro = Convert.ToInt32(objDRParametroDet["IDPARAMETRO"]);
                                    objParametroDetBE.Nombre = Convert.ToString(objDRParametroDet["NOMBRE"]);
                                    listaParametroDet.Add(objParametroDetBE);
                                }
                                objDRParametroDet.Close();
                            }
                            NewConexion.retClose();
                            return listaParametroDet;
                        }
                        catch (Exception ex)
                        {
                            string error = ex.ToString();
                            List<ParametroDetalleBE> listaParametroDet = new List<ParametroDetalleBE>();
                            return listaParametroDet;
                        }
                        finally
                        {
                            if (NewConexion.conexion.State == ConnectionState.Open) NewConexion.retClose();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int mInsertar(ParametroDetalleBE parametro)
        {

            try
            {
                Int32 id = 0;

                using (OracleCommand cmd = new OracleCommand())
                {

                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_INSERTAR_PARAMETRODETALLE";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("PNOMBRE", OracleType.VarChar);
                    OracleParameter prm2 = new OracleParameter("PIDTABLA", OracleType.Number);
                    OracleParameter prm3 = new OracleParameter("PUSUCRE", OracleType.VarChar);
                    OracleParameter prm4 = new OracleParameter("PIPREG", OracleType.VarChar);
                    OracleParameter prm5 = new OracleParameter("PIDPARAMETRO", OracleType.Number);

                    prm1.Direction = ParameterDirection.Input;
                    prm2.Direction = ParameterDirection.Input;
                    prm3.Direction = ParameterDirection.Input;
                    prm4.Direction = ParameterDirection.Input;                    
                    prm5.Direction = ParameterDirection.Output;

                    prm1.Value = parametro.Nombre;
                    prm2.Value = parametro.IdTabla;
                    prm3.Value = parametro.UsuCre;
                    prm4.Value = parametro.IpReg;
                    prm5.Value = parametro.IdParametro;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);
                    cmd.Parameters.Add(prm4);
                    cmd.Parameters.Add(prm5);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        int ret = NewConexion.ejecutaSQL(cmd);
                        id = Convert.ToInt32(Convert.ToString(cmd.Parameters["PIDPARAMETRO"].Value));
                        return id;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void mEliminar(ParametroDetalleBE parametro)
        {
            try
            {

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.CommandText = "PKG_HECO_GLOBAL.SP_ELIMINAR_PARAMETRODETALLE";
                    cmd.CommandType = CommandType.StoredProcedure;

                    OracleParameter prm1 = new OracleParameter("PIDPARAMETRO", OracleType.Int32);
                    OracleParameter prm2 = new OracleParameter("PUSUMOD", OracleType.VarChar);
                    OracleParameter prm3 = new OracleParameter("PIPMOD", OracleType.VarChar);

                    prm1.Direction = ParameterDirection.Input;                    
                    prm2.Direction = ParameterDirection.Input;                    
                    prm3.Direction = ParameterDirection.Input;

                    prm1.Value = parametro.IdParametro;
                    prm2.Value = parametro.UsuMod;
                    prm3.Value = parametro.IpMod;

                    cmd.Parameters.Add(prm1);
                    cmd.Parameters.Add(prm2);
                    cmd.Parameters.Add(prm3);

                    using (DL_CONEXION NewConexion = new DL_CONEXION())
                    {
                        int ret = NewConexion.ejecutaSQL(cmd);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
